package com.rybbaby.tss.front.tssMemberReturnPremium.dao.imp;

import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssMemberReturnPremium.dao.ITssMemberReturnPremiumDao;
import com.rybbaby.tss.front.tssMemberReturnPremium.entity.TssMemberReturnPremium;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @description 会员退费记录表数据库操作实现
 * 
 * @author 雷春琼
 * 
 * @createDate 2017-08-10 上午12:14:02
 */
@Repository
public class TssMemberReturnPremiumDaoImp extends GenericDao<TssMemberReturnPremium> implements ITssMemberReturnPremiumDao {

    @Override
    public List<TssMemberReturnPremium> findSumAmout(String stuId, int i) throws Exception{
        String sql="SELECT\n" +
                "\tSUM(tss_member_return_premium.amout) AS sumAmout\n" +
                "FROM\n" +
                "\ttss_member_return_premium\n" +
                "WHERE\n" +
                "\ttss_member_return_premium.memberId = ?\n" +
                "\tAND tss_member_return_premium.refundType = ?\n" +
                "\tAND tss_member_return_premium.refundStatus = ?\n";
        List<TssMemberReturnPremium> listBySql = this.findListBySql(sql, TssMemberReturnPremium.class, stuId,i,5);
        return listBySql;
    }

    @Override
    public Map<String, Object> findTodayRefund(String adminUserIds) {
        String sql = "select SUM(amout) AS sumAmout from tss_member_return_premium where to_days(createTime) = to_days(now()) AND tss_member_return_premium.adminUserId in ('"+adminUserIds+"') AND tss_member_return_premium.refundStatus = 5";
        System.out.println(sql);
        return this.findOneForJdbc(sql,null);
    }

    @Override
    public Map<String, Object> findMonthRefund(String adminUserIds) {
        String sql = "select SUM(amout) AS sumAmout from tss_member_return_premium where DATE_FORMAT( createTime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) AND tss_member_return_premium.adminUserId in ('"+adminUserIds+"') AND tss_member_return_premium.refundStatus = 5";
        System.out.println(sql);
        return this.findOneForJdbc(sql,null);
    }

    @Override
    public List<Map<String , Object>> packageRefundList(String memberId) {
        String sql = "SELECT\n" +
                "\ttss_payment.id AS id,\n" +
                "\ttss_payment.stuId AS stuId,\n" +
                "\ttss_payment.paymentType AS paymentType,\n" +
                "\ttss_payment.contractNumber AS contractNumber,\n" +
                "\ttss_payment.depositStatus AS depositStatus,\n" +
                "\ttss_payment.applyStatus AS applyStatus,\n" +
                "\ttss_payment.amount AS amount,\n" +
                "\ttss_member_package.id AS memberPackageId,\n" +
                "\ttss_member_package.sumPrice AS sumPrice,\n" +
                "\ttss_package. NAME AS packageName,\n" +
                "\tsys_admin_user.userName AS userName,\n" +
                "\ttss_member_return_premium.refundCode AS refundCode,\n" +
                "\ttss_member_return_premium.refundStatus AS refundStatus,\n" +
                "\tsum(\n" +
                "\t\ttss_member_return_premium.amout\n" +
                "\t) AS returnAmout\n" +
                "FROM\n" +
                "\ttss_payment\n" +
                "LEFT JOIN tss_member_package ON tss_payment.packageId = tss_member_package.id\n" +
                "LEFT JOIN tss_package ON tss_member_package.packageId = tss_package.id\n" +
                "LEFT JOIN tss_member_return_premium ON tss_member_return_premium.packageId = tss_member_package.id\n" +
                "LEFT JOIN sys_admin_user ON tss_member_return_premium.adminUserId = sys_admin_user.id\n" +
                "LEFT JOIN tss_student ON tss_student.id = tss_payment.stuId\n" +
                "where stuId = ? and  paymentType in (1,3) \n" +
                "GROUP BY\n" +
                "\ttss_payment.id";
        //and tss_member_return_premium.isDelete = 0
        return this.findForJdbc(sql,new Object[]{memberId});
    }

    @Override
    public List<Map<String , Object>> depositRefundList(String memberId) {
        String sql = "SELECT\n" +
                "\ttss_payment.id AS id,\n" +
                "\ttss_payment.stuId AS stuId,\n" +
                "\ttss_payment.paymentType AS paymentType,\n" +
                "\ttss_payment.contractNumber AS contractNumber,\n" +
                "\ttss_payment.depositStatus AS depositStatus,\n" +
                "\ttss_payment.applyStatus AS applyStatus,\n" +
                "\ttss_payment.amount AS amount,\n" +
                "\tsys_admin_user.userName AS userName,\n" +
                "\ttss_member_return_premium.refundCode AS refundCode,\n" +
                "\ttss_member_return_premium.refundStatus AS refundStatus,\n" +
                "\tsum(\n" +
                "\t\ttss_member_return_premium.amout\n" +
                "\t) AS returnAmout\n" +
                "FROM\n" +
                "\ttss_payment\n" +
                "LEFT JOIN tss_member_return_premium ON tss_member_return_premium.memberId = tss_payment.stuId AND tss_member_return_premium.refundType = tss_payment.paymentType\n" +
                "LEFT JOIN sys_admin_user ON tss_member_return_premium.adminUserId = sys_admin_user.id\n" +
                "LEFT JOIN tss_student ON tss_student.id = tss_payment.stuId\n" +
                "where stuId = ? and paymentType = 2 and depositStatus = 1\n" +
                "GROUP BY\n" +
                "\ttss_payment.id\n";
        return this.findForJdbc(sql,new Object[]{memberId});
    }

    @Override
    public List<Map<String , Object>> paymentRefundList(String memberId) {
        /*String sql = "SELECT\n" +
                "\ttss_payment.id AS id,\n" +
                "\ttss_payment.stuId AS stuId,\n" +
                "\ttss_payment.paymentType AS paymentType,\n" +
                "\ttss_payment.contractNumber AS contractNumber,\n" +
                "\ttss_payment.depositStatus AS depositStatus,\n" +
                "\ttss_payment.applyStatus AS applyStatus,\n" +
                "-- \ttss_payment.amount AS amount,\n" +
                "\tsys_admin_user.userName AS userName,\n" +
                "\ttss_member_return_premium.refundCode AS refundCode,\n" +
                "\ttss_member_return_premium.refundStatus AS refundStatus,\n" +
                "\tsum(tss_payment.amount) AS sumAmount,\n" +
                "\tsum(tss_member_return_premium.amout) AS returnAmout\n" +
                "FROM\n" +
                "\ttss_payment\n" +
                "LEFT JOIN tss_member_return_premium ON tss_payment.stuId = tss_member_return_premium.memberId AND tss_payment.paymentType =  tss_member_return_premium.refundType\n" +
                "LEFT JOIN sys_admin_user ON tss_member_return_premium.adminUserId = sys_admin_user.id\n" +
                "LEFT JOIN tss_student ON tss_student.id = tss_payment.stuId\n" +
                "WHERE\n" +
                "\tstuId = ?\n" +
                "AND paymentType IN (4, 5, 6)\n" +
                "AND tss_member_return_premium.refundStatus =5\n" +
                "GROUP BY\n" +
                "\ttss_payment.paymentType\n";*/
        String sql = "SELECT * FROM (\n" +
                "SELECT\n" +
                "\ttss_payment.id AS id,\n" +
                "\ttss_payment.stuId AS stuId,\n" +
                "\ttss_payment.paymentType AS paymentType,\n" +
                "\ttss_payment.contractNumber AS contractNumber,\n" +
                "\ttss_payment.depositStatus AS depositStatus,\n" +
                "\ttss_payment.applyStatus AS applyStatus,\n" +
                "\tsum(tss_payment.amount) AS sumAmount\n" +
                "FROM\n" +
                "\ttss_payment\n" +
                "WHERE\n" +
                "\tstuId = ?\n" +
                "AND paymentType IN (4, 5, 6)\n" +
                "GROUP BY\n" +
                "\ttss_payment.paymentType\n" +
                ")AS pay\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "\tSELECT\n" +
                "    tss_member_return_premium.memberId,\n" +
                "\t\ttss_member_return_premium.refundType,\n" +
                "\t\tsum(\n" +
                "\t\ttss_member_return_premium.amout\n" +
                "\t) AS returnAmout,\n" +
                "\tsys_admin_user.realName AS userName\n" +
                "\tFROM\n" +
                "\t\ttss_member_return_premium\n" +
                "\tLEFT JOIN sys_admin_user ON tss_member_return_premium.createId = sys_admin_user.id\n" +
                "\tWHERE\n" +
                "\t\tmemberId = ?\n" +
                "\tAND tss_member_return_premium.refundType IN (4, 5, 6)\n" +
                "\tAND tss_member_return_premium.refundStatus = 5\n" +
                "GROUP BY\n" +
                "\ttss_member_return_premium.refundType\n" +
                ")AS refund\n" +
                "ON pay.stuId = refund.memberId\n" +
                "AND pay.paymentType = refund.refundType";
        return this.findForJdbc(sql,new Object[]{memberId,memberId});
    }

    @Override
    public List<Map<String , Object>> registeryFeeRefundList(String memberId) {
        /*String sql = "SELECT\n" +
                "\ttss_payment.id AS id,\n" +
                "\ttss_payment.stuId AS stuId,\n" +
                "\ttss_payment.paymentType AS paymentType,\n" +
                "\ttss_payment.contractNumber AS contractNumber,\n" +
                "\ttss_payment.depositStatus AS depositStatus,\n" +
                "\ttss_payment.applyStatus AS applyStatus,\n" +
                "\ttss_payment.amount AS amount,\n" +
                "\tsys_admin_user.userName AS userName,\n" +
                "\ttss_member_return_premium.refundCode AS refundCode,\n" +
                "\ttss_member_return_premium.refundStatus AS refundStatus,\n" +
                "\tsum(\n" +
                "\t\ttss_member_return_premium.amout\n" +
                "\t) AS returnAmout\n" +
                "FROM\n" +
                "\ttss_payment\n" +
                "LEFT JOIN tss_member_return_premium ON tss_member_return_premium.memberId = tss_payment.stuId AND tss_member_return_premium.refundType = tss_payment.paymentType\n" +
                "LEFT JOIN sys_admin_user ON tss_member_return_premium.adminUserId = sys_admin_user.id\n" +
                "LEFT JOIN tss_student ON tss_student.id = tss_payment.stuId\n" +
                "where stuId = ? and paymentType = 7\n" +
                "GROUP BY\n" +
                "\ttss_payment.id";*/
        String sql = "SELECT\n" +
                "\ttss_payment.id AS id,\n" +
                "\ttss_payment.stuId AS stuId,\n" +
                "\ttss_payment.paymentType AS paymentType,\n" +
                "\ttss_payment.contractNumber AS contractNumber,\n" +
                "\ttss_payment.depositStatus AS depositStatus,\n" +
                "\ttss_payment.applyStatus AS applyStatus,\n" +
                "\ttss_payment.amount AS amount,\n" +
                "\tsys_admin_user.realName AS userName,\n" +
                "\ttss_member_return_premium.refundCode AS refundCode,\n" +
                "\ttss_member_return_premium.refundStatus AS refundStatus,\n" +
                "\tsum(\n" +
                "\t\ttss_member_return_premium.amout\n" +
                "\t) AS returnAmout\n" +
                "FROM\n" +
                "\ttss_payment\n" +
                "LEFT JOIN tss_member_return_premium ON tss_member_return_premium.paymentId = tss_payment.id\n" +
                "LEFT JOIN sys_admin_user ON tss_member_return_premium.createId = sys_admin_user.id\n" +
                "WHERE\n" +
                "\tstuId = ?\n" +
                "AND paymentType = 7\n" +
                "GROUP BY\n" +
                "\ttss_payment.id";
        return this.findForJdbc(sql,new Object[]{memberId});
    }
}
